﻿use master
go
drop database company
go
use company
create database company
 create table sectionInfo(
  sectionID int identity(1,1) primary key ,
  sectionName varchar(10) not null

 )
 insert into sectionInfo(sectionName)
 values('人力部'),('市场部'),('开发部'),('运营部'),('调研部')
 go 
 
 create table userInfo(
  userNO int identity (1,1) primary key not null,
  userName varchar(10) not null unique,
  userSex varchar(2) not null check (  userSex='男'or  userSex='女'),
  userAge int not null check(userAge>=1 and userAge<=100),
  userCity varchar(50) default('福建省龙岩市'),
  userSection int references sectionInfo(sectionID),
  userSalary decimal(5,2) default(0)
 )
 insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
 values ('里尔','男',12,'安徽省安庆市',1,5.000),
        ('李思','女',19,'福建省厦门市',2,5.0000),
		('李娜','女',20,'福建省厦门市',1,1.5000),
		('王大','男',19,'安徽省安庆市',2,1.5000),
		('忘记','男',13,'安徽省安庆市',3,5.000),
		('诸葛亮','男',21,'上海市嘉定区',4,6.5000),
		('小乔','女',20,'安徽省安庆市',5,1.5000),
		('魏山','男',22,'河南省驻马店',5,1.5000),
		('里尔1','女',32,'安徽省安庆市',2,5.300),
		('里尔2','女',22,'安徽省安庆市',5,5.200),
		('里尔3','女',22,'安徽省安庆市',3,4.000),
		('里尔4','男',22,'安徽省安庆市',4,1.5000),
		('里尔5','男',15,'安徽省安庆市',5,5.2000),
		('里尔6','女',19,'安徽省安庆市',1,5.0300),
		('里尔7','男',19,'安徽省安庆市',2,1.5000),
		('里尔8','女',21,'安徽省安庆市',1,1.5000),
		('里尔9','男',12,'安徽省安庆市',3,5.2000),
		('里尔10','男',22,'安徽省安庆市',4,5.0300),
		('里尔11','女',32,'安徽省安庆市',5,5.0100),
		('里尔12','男',62,'安徽省安庆市',4,5.200),
		('里尔13','男',42,'安徽省安庆市',1,5.1)
 go
 create table workInfo(
  workID int identity(1,1) primary key not null ,
  userID int references userInfo(userNO) not null,
  workTime datetime not null,
  workDescription varchar(40) not null check(workDescription='迟到'or workDescription='早退'or workDescription='旷工'or workDescription= '病假'or workDescription='事假'or workDescription='出勤')

 )
 insert into workInfo( userID,workTime,workDescription)
 values (1,'8:30','早退'),
		(2,'8:31','早退'),
		(3,'8:32','早退'),
		(4,'8:33','早退'),
		(5,'8:30','早退'),
		(6,'8:35','早退'),
		(7,'8:30','早退'),
		(8,'8:36','早退'),
		(9,'8:37','早退'),
		(10,'8:38','早退'),
		(11,'8:39','早退'),
		(12,'8:40','早退'),
		(13,'8:30','早退'),
		(14,'8:50','早退'),
		(15,'8:33','早退'),
		(16,'8:31','早退'),
		(17,'8:32','早退'),
		(18,'8:31','早退'),
		(19,'8:30','早退'),
		(20,'8:32','早退')
		--1.查询公司的部门数量
		select count(*) 部门数量 from sectionInfo
		--2.查询公司的员工数量
		select count(*) 员工数量 from userInfo
		--3.查询所有部门的员工数量和工资平均值
		select  avg(userSalary) from userInfo 
		group by sectionName
		--4.查询每个年龄的男女生人数
		select count(*) from userInfo 
		group by userSex
		--5.查询所有部门员工的平均薪资
		select avg(userSalary) 所有部门员工的平均薪资 from userInfo
		group by  sectionName
		--6.查询员工最高工资和最低工资的差距
		select max(userSalary)-min(userSalary) 员工最高工资和最低工资的差距 from userInfo
		--7.查询平均工资高于 8000 的部门 id 和它的平均工资. 
		  select avg(userSalary) , sectionName from userInfo  
		 group by sectionName
		 having avg(userSalary)>8000
		--8.查询公司员工工资的最大值，最小值，平均值，总和
		select   max(userSalary ), min(userSalary) , avg(userSalary), sum(userSalary)      from  userInfo
		--9. (有员工的城市)各个城市的平均工资
		  select avg(userSalary)  from userInfo
		  group by userCity
		--10.查询每个员工本月的出勤情况信息
		select * from userInfo
		group by userName